

'''
CREATE TABLE device_command (

    id SERIAL PRIMARY KEY,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -- 命令类型：1-设备控制，2-设备状态查询
    type VARCHAR(255) NOT NULL,
    -- 命令内容
    content VARCHAR(255) NOT NULL,
    -- 设备ID
    device_id INTEGER NOT NULL,

    CONSTRAINT fk_device_id
        FOREIGN KEY (device_id)
        REFERENCES devices (id)
        ON DELETE CASCADE
);

SELECT SETVAL('device_command_id_seq', (SELECT MAX(id) FROM device_command));
'''

from . import logger

class DeviceCommandTable:

    def __init__(self, connection, cursor):

        self.connection = connection
        self.cursor = cursor

    # 创建设备命令
    def create_device_command(self, type, content, device_id):
    
        try:
            self.cursor.execute("INSERT INTO device_command (type, content, device_id) VALUES (%s, %s, %s) RETURNING id", (type, content, device_id))

        except Exception as e:
            logger.error("Error creating device command: {}".format(e))
            return False, str(e)

        else:
            logger.info(f"Device command created successfully.")
            return True, self.cursor.fetchone()["id"]
        
    # 获取设备命令
    def get_device_command(self, device_id, limit=5, offset=0):

        try:
            self.cursor.execute("SELECT * FROM device_command WHERE device_id = %s ORDER BY id DESC LIMIT %s OFFSET %s", (device_id, limit, offset))
            device_commands = self.cursor.fetchall()

        except Exception as e:
            logger.error("Error getting device command: {}".format(e))
            return False, str(e)

        else:
            logger.info(f"Device command retrieved successfully.")
            return True, device_commands
        
    def count_device_command(self, device_id):
    
        try:
            self.cursor.execute("SELECT COUNT(*) FROM device_command WHERE device_id = %s", (device_id,))
            count = self.cursor.fetchone()["count"]

        except Exception as e:
            logger.error("Error counting device command: {}".format(e))
            return False, str(e)

        else:
            logger.info(f"Device command count retrieved successfully.")
            return True, count
    
    # 获取所有设备命令
    def get_all_device_command(self, type=None, content=None, device_id=None, limit=10, offset=0):
        
        sql = "SELECT * FROM device_command"
        params = []

        if type:
            sql += " WHERE type = %s"
            params.append(type)

        if content:
    
            if not params:
                sql += " WHERE content = %s"
            else:
                sql += " AND content = %s"

            params.append(content)

        if device_id:
        
            if not params:
                sql += " WHERE device_id = %s"
            else:
                sql += " AND device_id = %s"

            params.append(device_id)

        sql += " ORDER BY id DESC LIMIT %s OFFSET %s"
        params.append(limit)
        params.append(offset)

        try:
            self.cursor.execute(sql, params)
            device_commands = self.cursor.fetchall()

        except Exception as e:
            logger.error("Error getting all device command: {}".format(e))
            return False, str(e)

        else:
            logger.info(f"All device command retrieved successfully.")
            return True, device_commands
        
    def count_all_device_command(self, type=None, content=None, device_id=None):
        
        sql = "SELECT COUNT(*) FROM device_command"
        params = []
        if type:
            sql += " WHERE type = %s"
            params.append(type)

        if content:
            if not params:
                sql += " WHERE content = %s"
            else:
                sql += " AND content = %s"
            params.append(content)

        if device_id:
            if not params:
                sql += " WHERE device_id = %s"
            else:
                sql += " AND device_id = %s"
            params.append(device_id)

        try:
            self.cursor.execute(sql, params)
            count = self.cursor.fetchone()["count"]

        except Exception as e:
            logger.error("Error counting all device command: {}".format(e))
            return False, str(e)

        else:
            logger.info(f"All device command count retrieved successfully.")
            return True, count
        
    # 删除设备命令
    def delete_device_command(self, command_ids):
        
        try:
            self.cursor.execute("DELETE FROM device_command WHERE id IN %s", (tuple(command_ids),))
            self.connection.commit()

        except Exception as e:
            logger.error("Error deleting device command: {}".format(e))
            return False, str(e)

        else:
            logger.info(f"Device command {command_ids} deleted successfully.")
            return True, None
